from pgtools.plugins.db.DBManager import DBManager
# 1 导入DBManager
# from DBManager import DBManager
# 2 创建对象
db = DBManager()
# 3 连接到工作目录下的数据库db.sqlite3
db.connect()
# # 4 操作数据库
# db.select('xxx') # 查询
# db.insert('xxx') # 添加/插入
# db.update('xxx') # 更新
# db.delete('xxx') # 删除
# # 5 关闭连接
# db.close() # 可省略

print(db.select('user'))
print(db.select('department'))
r = db.select('user')
type(r)
r.row_count() # 获取行数
r.col_count() # 获取列数
r[0] # 获取第一行数据
r[0]['number'] # 获取第一行的number字段
[row['number'] for row in db.select('user')]
# 打印出user表中的number和role字段
print(db.select('user', ['number']))
[row['number'] for row in db.select('user', ['number'])]
# 打印出user表中的number和role字段
print(db.select('user', ['number', 'role']))
# 打印出user表中的number和role字段，并且role字段为user
print(db.select('user', [], [['role', '=', 'user']]))
print(db.select('user', [], [['number', '>', '1001']]))
print(db.select('user', ['number'], [['number', '<>', '1001']]))
# 打印出user表中的number字段，并且role字段为user
print(db.select('user', ['number'], [['role', 'user']]))

# 打印出user表中的所有字段，并且number字段大于1001
print(db.select('user', ['number'], [['number', '>', '1001'], ]))
print(db.select('user', ['number'], [['number', '=', '1001'], ]))
print(db.select('user', ['password'], [['number', '1001']]))
print(db.select('user', ['password'], {'number': '1001'}))

# 打印出user表中的number和role字段，并且role字段为user，并且按照number字段排序
print(db.select('user', ['number', 'role'], [['role', 'user']], ['number']))
print(db.select('user', ['number', 'role'], [['role', 'user']], ['number asc']))
print(db.select('user', ['number', 'role'], [['role', 'user']], ['number desc']))
print(db.select('user', ['role'], [['role', 'user']]))
print(db.select('user', ['role'], [['role', 'user']], distinct=True))
# 打印出user表中的number字段，并且number字段包含1
print(db.select('user', [], [['number', 'like', '%1%'], ]))
print(db.select('user', [], [['number', 'like', '1%'], ]))
print(db.select('user', [], [['number', 'between', '1000', '1005'], ]))
print(db.select('user', ['number', 'dep']))
print([(u['number'], d['name'])  for u in db.select('user', ['number', 'dep']) for d in db.select('department', ['name',], {'id':u['dep']})])
# 打印出user表的number和department表的name字段，条件为user表的dep字段和department表的id字段相等
print(db.selectx(['user', 'department'], ['user.number', 'department.name'], [['user.dep', 'department.id']]))
print(db.selectx(['user', 'department'], ['user.number', 'department.name'], [['user.dep', 'department.id']]).as_list())
# db.insert('user', number='2001', password='2001', online='0', role='user')

# db.update('user', [['number', '2001']], ip='127.0.0.1', online=1)
# print(db.select('user', [], [['number', '2001']]))
db.delete('user', [['number', '2001']])
# db.delete('user', [['number', '>','2001']])
# print(db.select('user'))
# db.count('user', [['number', '>', '1001']])
# result = db.select('user')
# print(len(result), result[6])
# for d in result:
#   print(d)


'''
cols = ('number', 'password', 'online', 'role')
values = [('2002', '2002', 0, 'admin'),
          ('2003', '2003', 0, 'user'),
          ('2004', '2004', 0, 'user'),
          ]
db.insert_batch('user', cols, values)

'''
